Conditional Selection of Grid Rows - the Where() function

When used on their own, most functions or placeholders that reference data in a grid will use all rows in the grid.  This may be a calculation function that produces the sum of values in a column, a Grid Mapping function that copies some or all of a row, or just a simple placeholder that pulls all values for reference.

The Where() function applies conditions when referencing a grid, so that only rows that meet the conditions will be included.

The format is as follows:

Subject Where(Condition)

  • The Subject is what will be modified by the Where function.  This can be a placeholder, another function, or a single parameter of another function.  The Where function will apply to the subject that directly precedes it.

    • For example:

    [[Placeholder]] Where(Condition)

    This will return all values from the specified column for each row that meets the conditions.

    Function(Column1, Column2) Where(Condition)

    With the Where function outside of the function parameters, it applies to the entire function.  The function will evaluate column1 and column2 for each row that meets the conditions.

    Function(Column1, Column2 Where(Condition))

    With the Where function inside the function parameters, it only applies to the preceding parameter.  The function will evaluate column1 for every row, but only evaluate column2 for each row that meets the conditions.

    Note: Care should be taken when applying conditions to parameters.  Using Where to omit a required parameter will result in an error.  However, two versions of a required parameter can be provided with conditions that use one version or the other (do not include additional commas).

  • The Condition is any conditional expression that evaluates to boolean values (True or False).  This can be a simple field containing boolean values, a function that returns a boolean value, a comparison between two values, or even multiple conditions that resolve to a single boolean result.

    • In general, at least one of these will be a field in the grid or a calculated value based on a field in the grid, producing separate results for each row.  The other value can be a grid field, calculated value, static value, or a top-level field (field outside of a grid).

    See the section on Building Conditional Expressions for information on operators, negative conditions, and combined conditions.

Examples:  

A column in a table has five values, 100, 200, 300, 400, and 500.  The following basic formula is used.

Sum([[NumberColumn]])

This sums up all values in the column and displays 1500.  If the following conditional formula is used instead:

Sum([[NumberColumn]]) Where([[NumberColumn]] > 300)

Only the fourth and fifth rows are greater than 300, so the calculated field sums those values and displays 900.

A column in a table has five values, 100, 200, 300, 400, and 500.  A placeholder is used to retrieve the values.  The results can be used for display, or as a parameter for another function.

[[NumberColumn]]

This returns all values in the column, "100|200|300|400|500".  If the placeholder is used with a condition instead:

[[NumberColumn]] Where([[NumberColumn]] < 300)

Only the first and second rows are less than 300, so the calculated field returns those values, "100|200".

The Round() function uses a parameter to determine how a value is rounded.  A condition can be applied to determine if the value should be rounded up or rounded down.

Round([[OriginalValue]], 1, 1 Where([[RoundUpOrDown]] = "Up") 2 Where([[RoundUpOrDown]] = "Down"))

As this is a required parameter, the conditions must use one value or the other, or the function will fail.  In this example, the two values could be pulled from a Radio Button control, ensuring that one or the other is selected.

If the condition were instead comparing if a value was more or less than a threshold value, one condition could verify if the value is less (<) than the threshold, but the other condition would need to check if the value is greater than or equal (>=) to the threshold.

Tip: The Where function is useful when copying data between tables (grid mapping).  See the Functions to Enter Data Into Grids section for configuration instructions and samples.